package com.seeyon.apps.yxxt.util;

import com.seeyon.ctp.util.JDBCAgent;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
	private static final Logger log = Logger.getLogger(JDBCUtil.class);

	public static List<Map> doQuery(String sql) {
		JDBCAgent jdbc = new JDBCAgent();
		List resultMap = new ArrayList();
		try {
			jdbc.execute(sql);
			return jdbc.resultSetToList();
		} catch (Exception e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			if (jdbc != null) {
				jdbc.close();
			}
		}
		return resultMap;
	}

	public static int doUpdateOrInsert(String sql) {
		int result = 0;
		List params = new ArrayList();
		JDBCAgent jdbc = new JDBCAgent();
		try {
			jdbc.batch1Prepare(sql);
			jdbc.batch2Add(params);
			result = jdbc.batch3Execute();
		} catch (Exception e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			if (jdbc != null) {
				jdbc.close();
			}
		}
		return result;
	}

	public static Connection getConnection4Oracle(Map<String, String> connectInfo) {
		Connection connector = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connector = DriverManager.getConnection((String) connectInfo.get("url"),
					(String) connectInfo.get("userName"), (String) connectInfo.get("passWord"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			log.info(e);
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		}
		return connector;
	}

	public static int executeUpdate4Oracle(String sql, Map<String, String> connectInfo) {
		Connection connector = getConnection4Oracle(connectInfo);
		Statement statement = null;
		int i = 0;
		try {
			statement = connector.createStatement();
			i = statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		return i;
	}

	public static void closeConnection4Oracle(Connection connector, Statement statement) {
		try {
			statement.close();
			connector.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		}
	}

	public static void closeConnection4Oracle(Connection connector, Statement statement, ResultSet resultSet) {
		try {
			resultSet.close();
			statement.close();
			connector.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		}
	}

	public static Connection getConnection4Oracle10() {
		log.info("进入数据库连接----------开始");
		Connection connector = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@192.168.10.251:1521:ORCL";
			String user = "cjtest";
			String password = "cjtest";
			connector = DriverManager.getConnection(url, user, password);
			log.info("进入数据库连接----------成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return connector;
	}

	public static Connection getMYSQLConnection() {
			Connection connector = null;
		try {
			String driver = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://127.0.0.1:3306/jiaotongjituan";
			String user = "root";
			String password = "was081200";
			Class.forName(driver);
			connector = DriverManager.getConnection(url, user, password);
			System.out.println("连接数据库成功----------");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return connector;
	}

	public static int executeUpdate4Oracle01(String sql) {
		System.out.println("调用查询方法----------");
		Connection connector = getConnection4Oracle10();
		System.out.println("数据库链接成功----------");
		Statement statement = null;
		int i = 0;
		try {
			statement = connector.createStatement();
			i = statement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		return i;
	}

	public static boolean executeUpdate4Oracle02(String sql) {
		Connection connector = getConnection4Oracle10();
		Statement statement = null;
		boolean ss = false;
		try {
			statement = connector.createStatement();
			ResultSet sss = statement.executeQuery(sql);
			if (sss.next()) {
				ss = true;
				String idStr = sss.getString(1);
				System.out.println("idStr1修改==========" + idStr);
			}
			System.out.println("检查结果集合==========" + statement.executeQuery(sql));
			System.out.println("ss==========" + ss);
			return ss;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		return false;
	}

	public static int executeUpdate4Oracle03(String sql) {
		Connection connector = getMYSQLConnection();
		Statement statement = null;
		String idStr = null;
		int idMax = 0;
		try {
			statement = connector.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			System.out.println("检查结果集合是否为空==========" + resultSet.next());
			if (resultSet.next()) {
				idStr = resultSet.getString(1);
				System.out.println("idStr1==========" + idStr);
				String idStr2 = resultSet.getString(2);
				System.out.println("idStr2==========" + idStr2);
				try {
					idMax = Integer.parseInt(idStr);
				} catch (NumberFormatException e) {
					e.printStackTrace();
					System.out.println("String转化为int失败！==========");
				}
			}

			System.out.println("idMax==========" + idMax);
			return idMax;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		return idMax;
	}

	public static List<Map<String, Object>> executeUpdate4Oracle05(String sql) {
		Connection connector = getConnection4Oracle10();
		List list = new ArrayList();
		Statement statement = null;
		ResultSet sss = null;
		try {
			statement = connector.createStatement();

			sss = statement.executeQuery(sql);
			ResultSetMetaData md = sss.getMetaData();
			int columnCount = md.getColumnCount();

			while (sss.next()) {
				Map rowData = new HashMap();
				for (int i = 1; i <= columnCount; ++i) {
					rowData.put(md.getColumnName(i), sss.getObject(i));
				}
				list.add(rowData);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		System.out.println("检查结果集合==========" + sss);
		return list;
	}

	public static List<Map<String, Object>> executeUpdate4Oracle04(String sql) {
		Connection connector = getConnection4SqlServer();
		List list = new ArrayList();
		Statement statement = null;
		ResultSet sss = null;
		try {
			statement = connector.createStatement();

			sss = statement.executeQuery(sql);
			ResultSetMetaData md = sss.getMetaData();
			int columnCount = md.getColumnCount();

			while (sss.next()) {
				Map rowData = new HashMap();
				for (int i = 1; i <= columnCount; ++i) {
					rowData.put(md.getColumnName(i), sss.getObject(i));
				}
				list.add(rowData);
			}

			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e);
		} finally {
			closeConnection4Oracle(connector, statement);
		}
		return list;
	}

	public static Connection getConnection4hrp(Map<String, String> connectInfo)
			throws SQLException, ClassNotFoundException {
		Connection connector = null;

		Class.forName("oracle.jdbc.driver.OracleDriver");
		connector = DriverManager.getConnection(connectInfo.get("url"), connectInfo.get("userName"),
				connectInfo.get("passWord"));

		return connector;
	}

	public static Connection getConnection4SqlServer() {
		Connection connector = null;
		try {
			// 124.71.192.192 172.18.2.55
			// com.microsoft.sqlserver.jdbc.SQLServerDriver
			// integratedSecurity=true;" + " -- 可以使用“集成身份验证”（Windows 身份验证的一部分）
			String dbURL = "jdbc:sqlserver://ukrd.synology.me:10073;DatabaseName=YKU9Date0319;encrypt=true;trustServerCertificate=true";
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connector = DriverManager.getConnection(dbURL, "sa", "Sa123456");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connector;
	}

	public static void main(String[] args) {
		String renyuansql = "SELECT * FROM ywy";
		List<Map<String, Object>> renyuanResultList = executeUpdate4Oracle04(renyuansql);
		int totle_count = renyuanResultList.size();
		log.info("人员档案totle_count===" + totle_count);
	}
}
